# -*- coding: utf-8 -*-
__author__ = 'sunnychou'
__date__ = '2019/7/19 10:27'
from common.orm_helper import *
from common.excel_helper import ExcelReader
from k3_data_report.import_tb.utils import fetch_tb_unique_his_data
from  common.logger_helper import  g_wlogger

##########################################
# 1 导入k3基础表  付款单-维护  BEGIN
##########################################
class  FuKuanList(Base):
    __tablename__ = "k3_fukd"  # 自定义数据库表名称
    id = Column(Integer, primary_key=True)  # 自增
    sjsj = Column(String(10), verbose_name='年月')
    danju_code = Column(String(50), verbose_name='单据号')
    status = Column(String(20), verbose_name='状态')
    danju_date = Column(String(50), verbose_name='单据日期')
    caiwu_date = Column(String(50), verbose_name='财务日期')
    hesuan_type =  Column(String(50), verbose_name='核算项目类别')
    supplier_code = Column(String(50), verbose_name='供应商代码')
    supplier_name = Column(String(150), verbose_name='供应商')
    duobibie = Column(String(5), verbose_name='多币别换算')
    bumen_code = Column(String(10), verbose_name='部门代码')
    bumen_name = Column(String(10), verbose_name='部门')
    bus_code =  Column(String(10), verbose_name='业务员代码')
    bus_name = Column(String(10), verbose_name='业务员')
    money_type = Column(String(10), verbose_name='币别')
    danju_jine = Column(String(20), verbose_name='单据金额')
    shenhe_name =  Column(String(20), verbose_name='审核人')
    fukuan_bibie = Column(String(20), verbose_name='付款币别')
    fukuan_jine1 = Column(String(20), verbose_name='付款金额')
    fukuan_jine = Column(String(20), verbose_name='付款金额(本位币)')
    jiesuan_bibie = Column(String(20), verbose_name='结算币别')
    shifu_jine =  Column(String(20), verbose_name='实付金额')
    shifu_jine2 = Column(String(20), verbose_name='实付金额(本位币)')
    zhekou_jine1 = Column(String(20), verbose_name='折扣金额')
    zhekou_jine2 = Column(String(20), verbose_name='折扣金额(本位币)')
    fukuan_type = Column(String(20), verbose_name='付款类型')
    year = Column(String(10), verbose_name='年份')
    jiesuan_fangshi = Column(String(200), verbose_name='结算方式')
    kaihu_yinghang =  Column(String(50), verbose_name='核算项目开户银行')
    yinhang_zhanghao = Column(String(50), verbose_name='核算项目银行账号')
    jiesuan_fangshi_code = Column(String(20), verbose_name='结算方式代码')
    jiesuan_code = Column(String(20), verbose_name='结算号')
    zhanghao = Column(String(50),verbose_name='账号')
    huilv =  Column(String(20),verbose_name='汇率')
    danju_jine_benwei =  Column(String(20),verbose_name='单据金额(本位币)')


def fukuan_process(excel_name, sheet_name):
    '''
    付款表处理过程
    :param excel_name:
    :param sheet_name:
    :return:
    '''
    ereader = ExcelReader(excel_name,sheet_name=sheet_name)
    headers = ereader.get_excel_headers()
    data_lists = ereader.get_excel_data_list()
    danju_date = data_lists[0][2]
    danju_month = "%s-%s" % (danju_date.split("-")[0], danju_date.split("-")[1])
    unique_select_sql = "select  distinct sjsj  from k3_fukd"
    unique_set = fetch_tb_unique_his_data(unique_select_sql)
    if danju_month in unique_set:
        already_info = f"fukuan_process month:{danju_month} already existed."
        g_wlogger.winfo(already_info)
        return

    for data_list in data_lists:
        danju_code = data_list[1]
        if danju_code == "":
            continue
        fukuan = FuKuanList(status=data_list[0], sjsj=danju_month, danju_code=danju_code,danju_date=data_list[2],
                        caiwu_date=data_list[3], hesuan_type=data_list[4], supplier_code=data_list[5],
                        supplier_name=data_list[6],  duobibie=data_list[7], bumen_code=data_list[8],
                        bumen_name=data_list[9],bus_code=data_list[10],bus_name=data_list[11],
                        money_type=data_list[12], danju_jine=data_list[13],shenhe_name=data_list[14],
                        fukuan_bibie=data_list[15],fukuan_jine1=data_list[16], fukuan_jine=data_list[17],
                        jiesuan_bibie = data_list[18],shifu_jine=data_list[19],shifu_jine2=data_list[20],
                        zhekou_jine1 = data_list[21], zhekou_jine2=data_list[22], fukuan_type=data_list[23],
                        year = data_list[24], jiesuan_fangshi=data_list[25],kaihu_yinghang=data_list[26],
                        yinhang_zhanghao = data_list[27], jiesuan_fangshi_code=data_list[28],jiesuan_code=data_list[29],
                        zhanghao=data_list[31], huilv=data_list[32], danju_jine_benwei=data_list[33]
                        )
        session.add(fukuan)
        session.commit()

    #print(f"{danju_month}付款单处理完毕！")
    fkd_succ = f"付款单 文件：{excel_name}， 日期：{danju_month}处理完毕！"
    g_wlogger.winfo(fkd_succ)



def fukuan_process_main(excel_name, sheet_name):
    try:
        fukuan_process(excel_name, sheet_name)
        return True
    except Exception as e:
        g_wlogger.werror(f"fukuan_process_main has error:{e} with excel:{excel_name}")
        session.rollback()
        return False

##########################################
# 1 导入k3基础表  付款单-维护   END
##########################################
